package com.codeying.filter;

import com.codeying.servlet.AbsServlet;
import com.codeying.utils.JdbcUtils;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

/**
 * 通过sql查询数据
 */
@WebServlet("/q")
public class SqlServlet extends AbsServlet {

    public static void main(String[] args) throws SQLException {
        List<LinkedHashMap<String, String>> queryMap = getQueryMap(
                "select '请访问正确的地址！' as 查询结果"
        );
        for (LinkedHashMap<String, String> map : queryMap) { System.out.println(map); }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String t = req.getParameter("t");//业务类型
        String sql = "select '请访问正确的地址！' as 查询结果", title = "查询";
        List<LinkedHashMap<String, String>> res = null;
        switch (t) {
            case "1": {
                title = "测试查询";
                sql = "select '测试' as test";
                break;
            }
           
        }
        //查询
        try {
            res = getQueryMap(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        req.setAttribute("title", title);//查询标题
        req.setAttribute("t", t);//查询类型
        req.setAttribute("res", res);//结果集
        req.getRequestDispatcher("/pages/query.jsp").forward(req, resp);
    }

    /**
     * 根据sql语句返回查询内容
     *
     * @param sql
     * @return List<LinkedHashMap < String, String>>
     * @throws SQLException
     */
    private static List<LinkedHashMap<String, String>> getQueryMap(String sql) throws SQLException {
        PreparedStatement preparedStatement = JdbcUtils.getConnection().prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery(sql);
        List<LinkedHashMap<String, String>> res = new ArrayList<>();
        while (resultSet.next()) {
            LinkedHashMap<String, String> hm = new LinkedHashMap<>();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int count = rsmd.getColumnCount();// 获取列的数量
            for (int i = 1; i <= count; i++) {
                String key = rsmd.getColumnLabel(i);
                String value = resultSet.getString(i);
                hm.put(key, value);
            }
            res.add(hm);
        }
        return res;
    }
}

